package edu.cmu.cs.webapp.ioms.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import edu.cmu.cs.webapp.ioms.dao.DataAccessException;
import edu.cmu.cs.webapp.ioms.dao.ProductDAO;
import edu.cmu.cs.webapp.ioms.models.Client;
import edu.cmu.cs.webapp.ioms.models.IOMSPackage;
import edu.cmu.cs.webapp.ioms.models.OrderStatus;
import edu.cmu.cs.webapp.ioms.models.Product;
import edu.cmu.cs.webapp.ioms.models.PurchaseOrder;

public class ProductDAOImpl extends DAO implements ProductDAO{

	public ProductDAOImpl() {
		// TODO Auto-generated constructor stub
		super();
	}
	
	public List<Product> getAllProd() throws DataAccessException{
		Connection con = null;
		Product prod = null;
        List<Product> prodlst = new ArrayList<Product>();
        try {
        	con = getConnection();
        	Statement stmt = con.createStatement();
        	String sql = "select * from product";
        	ResultSet rs = stmt.executeQuery(sql);
        	while(rs.next()){
        		prod = IOMSPackage.eINSTANCE.getIOMSFactory().createProduct();
        		
        		prod.setProd_id(rs.getInt("prod_id"));
        		prod.setProd_name(rs.getString("prod_name"));
        		prod.setProd_description(rs.getString("prod_description"));
        		prod.setPrice(rs.getDouble("price"));

        		prodlst.add(prod);
        	}
            stmt.close();
            con.close();    
            
        } catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
        }
        return prodlst;
	}



	@Override
	public boolean deleteProd(Product prod) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		try {
			con = getConnection();		
            PreparedStatement pstmt = con.prepareStatement(
            "delete from product where prod_id =" + prod.getProd_id());
            pstmt.executeUpdate();
            System.out.println("delete: " + prod.toString());
            		
			pstmt.close();
			con.close();
			
			log("Delete product [" + prod.toString() + "]", "Delete");

		} catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
        	throw new DataAccessException(e);
		}
		return true;
	}

	@Override
	public Product getProduct(Product prod) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;

        try {
        	con = getConnection();
        	Statement stmt = con.createStatement();
        	String sql = "select * from product where prod_id=" + prod.getProd_id();
        	ResultSet rs = stmt.executeQuery(sql);
        	if(rs.next()){
        		prod.setPrice(rs.getDouble("price"));
        		prod.setProd_description(rs.getString("prod_description"));
        		prod.setProd_name(rs.getString("prod_name"));
        	}
            stmt.close();
            con.close();    	
        } catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
        }
        return prod;
	}



	@Override
	public boolean updateProd(Product prod) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		try {
			con = getConnection();
			
			String str="update product set ";
			if( prod.getProd_name()!= null )
				str += "prod_name='" + prod.getProd_name() + "',";
			if( prod.getProd_description() != null)
				str += "prod_description='" + prod.getProd_description() +"',";
			if( prod.getPrice() != 0)
				str += "price=" + prod.getPrice() +",";

			str = str.substring(0, str.lastIndexOf(","));
			str += " where prod_id=" + prod.getProd_id();	
			System.out.println(str);
            PreparedStatement pstmt = con.prepareStatement(str);
            pstmt.executeUpdate();
            System.out.println("update: " + prod.toString());
            		
			pstmt.close();
			con.close();
			
			log("Update product [" + prod.toString() + "]", "Update");

		} catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
		}
		return true;
	}

	@Override
	public boolean containProd(Product prod) throws DataAccessException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean addProd(Product prod) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;

		try {
			con = getConnection();
			
            PreparedStatement pstmt = con.prepareStatement(
            "insert into product(prod_name, prod_description, price) " +
            "values (? ,?, ?)");
            
            pstmt.setString(1, prod.getProd_name());
            pstmt.setString(2, prod.getProd_description());
            pstmt.setDouble(3, prod.getPrice());
            
            pstmt.executeUpdate();
            System.out.println("insert: " + prod.toString());
			          		
			pstmt.close();
			con.close();
			
			log("Add product [" + prod.toString() + "]", "Add");

			return true;
		} catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);           
		}
	}



}
